package worklog.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.TreeMap;

import worklog.dto.ProjectActivityDTO;
import worklog.dto.ProjectChartDTO;




public class ProjectChartDB {
	private MyDB db;

    public ProjectChartDB(){ 
    	try { db = new MyDB(); }
    	catch(Exception e){ db = null; }
    }
	protected void finalize(){ db.close(); }

	public void close(){ db.close(); }
	
	public Boolean isReady(){ return (this.db!=null); }

	/**
	 * Get project activity (hours per day)
	 * @param id	project ID
	 */
	public ProjectActivityDTO projectActivity(int id) throws SQLException {
		PreparedStatement statement = null;
		ProjectActivityDTO project = new ProjectActivityDTO();
		ResultSet res = null;
		TreeMap<String, Integer> rows = new TreeMap<String, Integer>();
		
		// get project details
		statement = db.connection.prepareStatement("SELECT * FROM project WHERE project.id = ?");
		statement.setInt(1, id);
		res = statement.executeQuery();		
		if (res != null && res.next()) project.set(res);
		statement.close();
		
		// get rows
		statement = db.connection.prepareStatement("SELECT COUNT(id) AS hours, day FROM entry WHERE projectID = ? GROUP BY day ORDER BY day ASC");
		statement.setInt(1, id);
		res = statement.executeQuery();
		while (res.next()) rows.put(res.getString("day"), res.getInt("hours"));
		statement.close();
		
		project.setRows(rows);
		
		return project;
	}	
	
	
	/**
	 * Most active projects in the last 30 days
	 * @throws SQLException 
	 */
	public ArrayList<ProjectChartDTO> mostActive() throws SQLException {
		Statement statement = db.connection.createStatement();
		ResultSet res = statement.executeQuery("SELECT project.name, projectID, count(entry.id) AS hours " + 
				"FROM entry LEFT JOIN project ON project.id = projectID " +
				"WHERE projectID <> 0 AND day BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() " + 
				"GROUP BY projectID ORDER BY hours DESC LIMIT 0,10");

		ArrayList<ProjectChartDTO> items = new ArrayList<ProjectChartDTO>();
		while (res.next()){
			ProjectChartDTO project = new ProjectChartDTO();
			project.set(res);
			items.add(project);
		}
		statement.close();
		
		if (items.size() > 0) return items;
		return null;
	}

	
	/**
	 * Most hours spent on projects in the last 30 days
	 * @throws SQLException 
	 */
	public ArrayList<ProjectChartDTO> mostHours() throws SQLException {
		Statement statement = db.connection.createStatement();
		ResultSet res = statement.executeQuery("SELECT project.name, projectID, count(entry.id) AS hours " + 
				"FROM entry LEFT JOIN project ON project.id = projectID " + 
				"WHERE projectID <> 0  GROUP BY projectID ORDER BY hours DESC LIMIT 0,10");
		
		ArrayList<ProjectChartDTO> items = new ArrayList<ProjectChartDTO>();
		while (res.next()){
			ProjectChartDTO project = new ProjectChartDTO();
			project.set(res);
			items.add(project);
		}
		statement.close();
		
		if (items.size() > 0) return items;
		return null;
	}
	
	/**
	 * Return how many hours user spent on top projects
	 * @param userId	
	 * @param days		if empty, return all times values
	 */
	public ArrayList<ProjectChartDTO> hoursPerProject(int userId, String days) throws SQLException {
		PreparedStatement statement;
		
		if (days.isEmpty()){			// all times
			statement = db.connection.prepareStatement("SELECT project.name, projectID, count(entry.id) AS hours " + 
					"FROM entry LEFT JOIN project ON project.id = projectID " + 
					"WHERE projectID <> 0 AND entry.userId = ? GROUP BY projectID ORDER BY hours DESC LIMIT 0,10");
			statement.setInt(1, userId);
		}
		else {							// since the date
			statement = db.connection.prepareStatement("SELECT project.name, projectID, count(entry.id) AS hours " + 
					"FROM entry LEFT JOIN project ON project.id = projectID " + 
					"WHERE projectID <> 0 AND entry.userId = ? AND entry.day > ? GROUP BY projectID ORDER BY hours DESC LIMIT 0,10");
			statement.setInt(1, userId);
			statement.setString(2, days);
		}
		
		ResultSet res = statement.executeQuery();
		
		ArrayList<ProjectChartDTO> items = new ArrayList<ProjectChartDTO>();
		while (res.next()){
			ProjectChartDTO project = new ProjectChartDTO();
			project.set(res);
			items.add(project);
		}
		statement.close();
		
		if (items.size() > 0) return items;
		return null;
	}
	

}
